from fastapi import APIRouter, Request, HTTPException
from fastapi.responses import StreamingResponse, JSONResponse
from pydantic import BaseModel

from database.database import engine
from sqlalchemy import text

from utils.smart_sales_db import select_category_info


router = APIRouter(
    prefix='/goods',
    tags = ['产品信息']
)

def select_goods_category_count(categoryId):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select count(*) from smart_sales_goods where categoryId = :categoryId;
        ''')
        result = conn.execute(sql, [{'categoryId': categoryId}]).fetchone()[0]
    return result
    # except:
    #   pass


def select_goods_category_limit(categoryId, limit):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select * from smart_sales_goods where categoryId in (select id from smart_sales_category where id = :categoryId or parent = :categoryId) order by orderNum desc limit :limit;
        ''')
        result = conn.execute(sql, [{'categoryId': categoryId, 'limit': limit}]).fetchall()
    return result
    # except:
    #   pass


def select_goods_temporary(query):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select * from smart_sales_goods where categoryId = :categoryId order by :sortField desc limit :from, :size;
        ''')
        result = conn.execute(sql, 
            [
                {
                    'categoryId': query["categoryId"],
                    'sortField': query["sortField"], 
                    'from': (query["page"] - 1) * query["pageSize"], 
                    'size': query["pageSize"]
                }
            ]
        ).fetchall()
    return result
    # except:
    #   pass


def select_goods_relevant(limit):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select * from smart_sales_goods limit :limit;
        ''')
        result = conn.execute(sql, [{'limit': limit}]).fetchall()
    return result
    # except:
    #   pass


def select_goods_info(id):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select t1.id,t1.categoryId,t1.name,t1.description,t1.price,t1.picture,
               t2.oldPrice,t2.goodsDiscount,t2.goodsInventory,t2.brandId,t2.commentCount,t2.salesCount,t2.collectCount,t2.mainVideos,
               t2.mainPictures,t2.detailsId,t2.specId,t2.skusId,t2.videoScale,t2.isPresale,t2.isCollect,t2.recommends,t2.evluationInfoId
        from smart_sales_goods t1 
        left join smart_sales_goods_particulars t2 
        on t2.goodsId = t1.id 
        where t1.id = :id;
        ''')
        result = conn.execute(sql, [{'id': id}]).fetchone()
    return result
    # except:
    #   pass


def select_goods_detail(id):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select *
        from smart_sales_goods_details t1 
        where t1.id = :id;
        ''')
        result = conn.execute(sql, [{'id': id}]).fetchone()
    return result
    # except:
    #   pass


def select_goods_property(ids):
    # try:
    with engine.connect() as conn:
        sql = text(f'''
        select *
        from smart_sales_goods_property t1 
        where t1.id in (''' + ids + ''');
        ''')
        result = conn.execute(sql).fetchall()
    return result
    # except:
    #   pass


@router.get("")
def goods_info(id: int):
    result = select_goods_info(id)
    if result:
        goods_dict = result._asdict()
        categoryId = goods_dict["categoryId"]
        categories = []

        while categoryId:
            category = select_category_info(categoryId)._asdict()
            if len(categories) > 0:
                categories[-1]["parent"] = category
            categoryId = category["parent"]
            categories.append(category)

        details_dict = select_goods_detail(goods_dict["detailsId"])._asdict()
        details = {}
        details["properties"] = [row._asdict() for row in select_goods_property(details_dict["detailsProperty"])]
        details["pictures"] = details_dict["detailsPictures"].split(",")
        goods_dict["categories"] = categories
        goods_dict["details"] = details
        goods_dict["mainPictures"] = goods_dict["mainPictures"].split(",")

        data = { "code": 0, "message": "操作成功" }
        data["result"] = goods_dict
        return JSONResponse(status_code=200, content=data)
    else:
        data = { "code": 2, "msg": "操作失败" }
        return JSONResponse(status_code=201, content=data)


@router.get("/relevant")
def goods_relevant(limit: int):
    result = select_goods_relevant(limit)
    if result:
        result_dict = [row._asdict() for row in result]

        data = { "code": 0, "message": "操作成功" }
        data["result"] = result_dict
        return JSONResponse(status_code=200, content=data)
    else:
        data = { "code": 2, "msg": "操作失败" }
        return JSONResponse(status_code=201, content=data)
